#!/usr/bin/python

help = """
normalise data from a spreadsheet, by which I mean...

you have a spreadsheet (XLS file) that looks like this

     A   B   C   D   D

1       Foo Bar Baz Qux
2
3   Moo  23   3   3  12
4   Woo   1  23  23   2
5   Zoo   3  33  44   3

and you want to read this into a database, so you need to normalise it thus:

1 Foo Moo 23
2 Bar Moo  3
3 Baz Moo  3
4 Qux Moo 12
5 Foo Woo  1
6 Bar Woo 23
7 Baz Woo 23

and so on. Basically there's a row and column with labels, and then a grid of values.
The output is each element in the grid with its corresponding row and column label. 
Optionally you can add a unique id column.

The output goes to stdout and is CSV format. You can change the separator and choose a
quoting character to quote all output fields. You can set the initial id, so that if you
have several sheets in a file you can append them and set the id to be unique. Also, it
doesn't output headers, so output them yourself and append the output. This example shows 
how to do that, and normalises a 3x3 grid from two sheets:

 echo "id,x,y,m" > output.txt
 python normaliser.py -id=1 file.xls 1 B2:D4 A 1 >>output.txt
 python normaliser.py -id=10 file.xls 2 B2:D4 A 1 >>output.txt

You can specify sheet by name or number, starting at 1.

Requirements: Python, and both the xlrd and pyExcelerator modules. xlrd is used for reading
and pyExcelerator has some nice Utils for converting cell names.

"""

import xlrd
from pyExcelerator import Utils
import sys
from datetime import datetime

from optparse import OptionParser


def opts():
    usage = "usage: %prog <ExcelFile> <Sheet> <Area> <ColLabel> <RowLabel>  [> Output]"
    parser = OptionParser(usage)
    parser.add_option("-i","--id", default=None,dest="id",help="initial id")
    parser.add_option("-s","--sep", dest="sep",help="separator",metavar="SEP", default=",")
    parser.add_option("-q","--quote", dest="quote",help="quote character",metavar="QUOTE", default="")
    (options,args)=parser.parse_args()
    if len(args) < 5:
        parser.error("Not enough arguments")
    if len(args) > 5:
        parser.error("Too many arguments")
    (options.input, options.sheet, options.area,
     options.colLabel,options.rowLabel) = args[:5]

    try:
        options.area=Utils.cellrange_to_rowcol_pair(options.area)
    except:
        parser.error("Error parsing area spec "+options.area)

    options.colLabel = Utils.col_by_name(options.colLabel)
    options.rowLabel = int(options.rowLabel)-1

    return options

def main():
    options = opts()
    book = xlrd.open_workbook(options.input,logfile=sys.stderr)
    if options.sheet.isdigit():
        sh = book.sheet_by_index(int(options.sheet)-1)
    else:
        sh = book.sheet_by_name(options.sheet)

    if options.id:
        id = int(options.id)
    else:
        id = 1
    for row in range(options.area[0],options.area[2]+1):
        rvs = sh.row_values(row)
        for col in range(options.area[1],options.area[3]+1):
            if options.id: 
                sys.stdout.write( options.quote+str(id)+options.quote+options.sep )
            print options.sep.join([str(i) for i in scv(sh,row,options.colLabel,options),scv(sh,options.rowLabel,col,options),scv(sh,row,col,options)])
            id = id + 1
    

def scv(sh,row,col,options):
    cell = sh.cell(rowx=row,colx=col)
    type=cell.ctype
    value=cell.value
    if type == 3:
        d = xlrd.xldate_as_tuple(value,sh.book.datemode)
        return options.quote+str(datetime(d[0],d[1],d[2],d[3],d[4],d[5]))+options.quote
    else:
        return options.quote+str(value)+options.quote

if __name__ == "__main__":
    main()

